package cn.com.cifi.mars.util;

import cn.com.cifi.mars.bean.vo.signPlan.DmAnalyticalVo;
import cn.com.cifi.mars.bean.vo.signPlan.PastYearVo;
import cn.com.cifi.mars.bean.vo.signPlan.QuarterSignVo;
import cn.com.cifi.mars.bean.vo.signPlan.YearTotalVo;
import cn.com.cifi.mars.bean.vo.signPlan.signOverall.MonthSignVo;
import cn.com.cifi.mars.bean.vo.signPlan.signOverall.YearSignVo;
import cn.com.cifi.mars.constant.Constants;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.stream.Collectors;

/**
 *
 * 类名称：DmAnalyticalTemplate
 * 类描述：供销存综合分析表模板
 * 创建人：jyx
 * 创建时间：2020年03月24日
 * @version 1.0.0
 */

@Slf4j
public class DmAnalyticalTemplate {
    private static final String TEMPLATE_PATH = "/template/dm_analytical.xlsx";

    private XSSFWorkbook workbook;

    private XSSFSheet sheet;

    //默认sheet索引
    private int defaultSheetIndex = 0;
    //数据开始行
    private int dataBeginRow = 4;
    //当前行标
    private int currentDataRowCursor = dataBeginRow;
    //年度合计列模板开始索引
    private int yeardynamicColumnBeginIndex = 740;
    //当前列标
    private int currentDataColumnCursor = yeardynamicColumnBeginIndex;
    //单元格样式
    private XSSFCellStyle cellStyle;
    //表头单元格样式
    private XSSFCellStyle cellStyleZ;
    //汇总行文字单元格样式
    private XSSFCellStyle cellStyleA1;
    //汇总行权益比例单元格样式
    private XSSFCellStyle cellStyleA2;
    //汇总行数值单元格样式(数字-两位小数)
    private XSSFCellStyle cellStyleA3;
    //汇总行数值单元格样式（数字-0位小数）
    private XSSFCellStyle cellStyleA4;
    //汇总行字符串数值单元格样式
    private XSSFCellStyle cellStyleA5;
    //汇总行百分比单元格样式
    private XSSFCellStyle cellStyleA6;

    //数据行文字单元格样式
    private XSSFCellStyle cellStyleB1;
    //数据行权益比例单元格样式
    private XSSFCellStyle cellStyleB2;
    //数据行数值单元格样式(数字-两位小数)
    private XSSFCellStyle cellStyleB3;
    //数据行数值单元格样式（数字-0位小数）
    private XSSFCellStyle cellStyleB4;
    //数据行百分比单元格样式
    private XSSFCellStyle cellStyleB5;

    //权益比例
    private static final String EQUITY_RATIO  = "equityRatio";
    // 数字-两位小数
    private static final String NUMERIC_TWO  = "numericByTwo";
    //数字-0位小数
    private static final String NUMERIC_ZERO  = "numericByZero";
    //达成率
    private static final String ACHIEVING_RATE   = "achievingRate ";

    //套数、面积、均价单元格宽度
    private int columnWidthA;
    //百分比单元格宽度
    private int columnWidthB;
    //金额单元格宽度
    private int columnWidthC;


    public DmAnalyticalTemplate(List<DmAnalyticalVo> voList) {
        List<YearTotalVo> yearTotalVoList = voList.get(0).getYearTotalVo();
        List<String> yearList = yearTotalVoList.stream().map(s -> s.getYear()).collect(Collectors.toList());

        int yearInt = Integer.valueOf(voList.get(0).getYearSignVo().getYear());
        try {
            InputStream tempInputStream = DmAnalyticalTemplate.class.getResourceAsStream(TEMPLATE_PATH);
            if (tempInputStream != null) {
                workbook = new XSSFWorkbook(tempInputStream);
                sheet = workbook.getSheetAt(defaultSheetIndex);
                XSSFRow row = sheet.getRow(defaultSheetIndex);

                //当年以前列开始索引:19      宽度:15
                //当年月度列开始索引:34      宽度:50
                //当年年度列开始索引:634     宽度:38
                //明年季度列开始索引:672     宽度:17
                //年度合计列开始索引:740     宽度:17
                row.getCell(19).setCellValue(yearInt-1 +"年及以前");
                row.getCell(34).setCellValue(yearInt +"年1月");
                row.getCell(34+50).setCellValue(yearInt +"年2月");
                row.getCell(84+50).setCellValue(yearInt +"年3月");
                row.getCell(134+50).setCellValue(yearInt +"年4月");
                row.getCell(184+50).setCellValue(yearInt +"年5月");
                row.getCell(234+50).setCellValue(yearInt +"年6月");
                row.getCell(284+50).setCellValue(yearInt +"年7月");
                row.getCell(334+50).setCellValue(yearInt +"年8月");
                row.getCell(384+50).setCellValue(yearInt +"年9月");
                row.getCell(434+50).setCellValue(yearInt +"年10月");
                row.getCell(484+50).setCellValue(yearInt +"年11月");
                row.getCell(534+50).setCellValue(yearInt +"年12月");
                row.getCell(634).setCellValue(yearInt +"年年度合计");
                row.getCell(672).setCellValue(yearInt +"年一季度合计");
                row.getCell(672+17).setCellValue(yearInt +"年二季度合计");
                row.getCell(689+17).setCellValue(yearInt +"年三季度合计");
                row.getCell(706+17).setCellValue(yearInt +"年四季度合计");

                cellStyleZ = row.getCell(0).getCellStyle();
                cellStyleA1 = sheet.getRow(4).getCell(0).getCellStyle();
                cellStyleA2 = sheet.getRow(4).getCell(7).getCellStyle();
                cellStyleA3 = sheet.getRow(4).getCell(19).getCellStyle();
                cellStyleA4 = sheet.getRow(4).getCell(20).getCellStyle();
                cellStyleA5 = sheet.getRow(4).getCell(22).getCellStyle();
                cellStyleA6 = sheet.getRow(4).getCell(31).getCellStyle();
                cellStyleB1 = sheet.getRow(5).getCell(0).getCellStyle();
                cellStyleB2 = sheet.getRow(5).getCell(7).getCellStyle();
                cellStyleB3 = sheet.getRow(5).getCell(19).getCellStyle();
                cellStyleB4 = sheet.getRow(5).getCell(22).getCellStyle();
                cellStyleB5 = sheet.getRow(5).getCell(31).getCellStyle();

                columnWidthA = sheet.getColumnWidth(0);
                columnWidthB = sheet.getColumnWidth(7);
                columnWidthC = sheet.getColumnWidth(22);

                for (String year : yearList){
                    if(currentDataColumnCursor == 740) {
                        XSSFCell cell = row.getCell(currentDataColumnCursor);
                        cell.setCellValue(year + "年年度合计");
                        cell.setCellStyle(cellStyleZ);
                    }else if (currentDataColumnCursor != 740) {
                        //创建单元格
                        for (int i = 0; i < 17; i++) {
                            row.createCell(currentDataColumnCursor + i);
                        }

                        //合并单元格
                        sheet.addMergedRegion(new CellRangeAddress(0, 0, currentDataColumnCursor, currentDataColumnCursor+16));
                        XSSFCell cell = row.getCell(currentDataColumnCursor);
                        cell.setCellValue(year + "年年度合计");
                        cell.setCellStyle(cellStyleZ);
                        //标题行-动态扩展
                        cellDynamicPropagation(currentDataColumnCursor,"year");
                    }
                    currentDataColumnCursor+=17;
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * 单元格动态扩展
     * @param index 起始坐标
     * @param type 扩展类型(year:年，month:月)
     */
    public void cellDynamicPropagation(int index,String type){
        XSSFRow rowOne = sheet.getRow(1);
        XSSFRow rowTwo = sheet.getRow(2);
        XSSFRow rowThree = sheet.getRow(3);

        if(type.equals("year")) {
            for (int i = 0; i < 17; i++) {
                XSSFCell cellOne = rowOne.createCell(index + i);
                XSSFCell cellTwo = rowTwo.createCell(index + i);
                XSSFCell cellThree = rowThree.createCell(index + i);
                cellOne.setCellStyle(cellStyleZ);
                cellTwo.setCellStyle(cellStyleZ);
                cellThree.setCellStyle(cellStyleZ);
            }

            //标题行,单元格处理
            sheet.addMergedRegion(new CellRangeAddress(1, 2, index, index+3));
            rowOne.getCell(index).setCellValue("年初存货");
            sheet.addMergedRegion(new CellRangeAddress(1, 2, index+4, index+7));
            rowOne.getCell(index+4).setCellValue("年度新供");
            sheet.addMergedRegion(new CellRangeAddress(1, 2, index+8, index+11));
            rowOne.getCell(index+8).setCellValue("年度可售");
            sheet.addMergedRegion(new CellRangeAddress(1, 2, index+12, index+15));
            rowOne.getCell(index+12).setCellValue("年度签约计划");
            sheet.addMergedRegion(new CellRangeAddress(1, 3, index+16, index+16));
            rowOne.getCell(index+16).setCellValue("计划去化率（%）");
            //标题行2,单元格处理
            rowThree.getCell(index).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+1).setCellValue("面积\n(平米)");
            rowThree.getCell(index+2).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+3).setCellValue("金额\n(万元)");
            rowThree.getCell(index+4).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+5).setCellValue("面积\n(平米)");
            rowThree.getCell(index+6).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+7).setCellValue("金额\n(万元)");
            rowThree.getCell(index+8).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+9).setCellValue("面积\n(平米)");
            rowThree.getCell(index+10).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+11).setCellValue("金额\n(万元)");
            rowThree.getCell(index+12).setCellValue("套数/车位数\n(套/个)");
            rowThree.getCell(index+13).setCellValue("面积\n(平米)");
            rowThree.getCell(index+14).setCellValue("均价\n(元/平米,个)");
            rowThree.getCell(index+15).setCellValue("金额\n(万元)");

            sheet.setColumnWidth(index, columnWidthA);
            sheet.setColumnWidth(index+1, columnWidthA);
            sheet.setColumnWidth(index+2, columnWidthA);
            sheet.setColumnWidth(index+3, columnWidthC);
            sheet.setColumnWidth(index+4, columnWidthA);
            sheet.setColumnWidth(index+5, columnWidthA);
            sheet.setColumnWidth(index+6, columnWidthA);
            sheet.setColumnWidth(index+7, columnWidthC);
            sheet.setColumnWidth(index+8, columnWidthA);
            sheet.setColumnWidth(index+9, columnWidthA);
            sheet.setColumnWidth(index+10, columnWidthA);
            sheet.setColumnWidth(index+11, columnWidthC);
            sheet.setColumnWidth(index+12, columnWidthA);
            sheet.setColumnWidth(index+13, columnWidthA);
            sheet.setColumnWidth(index+14, columnWidthA);
            sheet.setColumnWidth(index+15, columnWidthC);
            sheet.setColumnWidth(index+16, columnWidthB);
        }
    }


    public void setSheetDate(String name) {
        workbook.setSheetName(defaultSheetIndex,name);
    }


    /**
     * 批量插入行
     *
     * @param list 报表数据
     */
    public synchronized void appendRows(List<DmAnalyticalVo> list) {
        for (DmAnalyticalVo vo : list) {
            this.appendRow(vo);
        }
    }

    /**
     * 插入行
     *
     * @param
     */
    public synchronized void appendRow(DmAnalyticalVo vo) {
        XSSFRow row = sheet.getRow(currentDataRowCursor);
        if (null == row) {
            row = sheet.createRow(currentDataRowCursor);
        }
        row.setHeightInPoints(Constants.HEIGHT_POINTS);
        boolean isParentLevel;
        if(null == vo.getProductName()){
            isParentLevel = true;
        }else {
            isParentLevel = false;
        }
        int index = 0;
        this.appendCell(row, index++,vo.getBusinessDivisionName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getCityCompanyName(),isParentLevel,"");
        //项目信息
        this.appendCell(row, index++,vo.getProjectName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProjectCode(),isParentLevel,"");
        this.appendCell(row, index++,null == vo.getIsFirstOpen() ? "" : vo.getIsFirstOpen() == 1?"首开":vo.getIsContinueOpen() == 1?"续销":"",isParentLevel,"");
        //分期信息
        this.appendCell(row, index++,vo.getStageName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getStageCode(),isParentLevel,"");
        this.appendCell(row, index++,vo.getEquityRatio(),isParentLevel,EQUITY_RATIO);
        this.appendCell(row, index++,vo.getOpmarketing(),isParentLevel,"");
        this.appendCell(row, index++,null == vo.getIsAndTable() ? "" :  vo.getIsAndTable() == 1?"是":"否",isParentLevel,"");
        this.appendCell(row, index++,null == vo.getExistingHouses() ? "" : vo.getExistingHouses() == 1?"现房销售":"非现房销售",isParentLevel,"");
        //组团信息
        this.appendCell(row, index++,vo.getGroupName(),isParentLevel,"");
        //产品信息
        this.appendCell(row, index++,vo.getProProductName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProProductCode(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProductName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProductCode(),isParentLevel,"");
        this.appendCell(row, index++,vo.getBusinessTypeName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getFreeTypeName(),isParentLevel,"");
        this.appendCell(row, index++,vo.getProTypeName(),isParentLevel,"");

        //当年以前签约
        PastYearVo pastYearVo = vo.getPastYearVo();
        this.appendCell(row, index++,pastYearVo.getAvailableNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,pastYearVo.getAvailableArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,pastYearVo.getAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,pastYearVo.getAvailablePrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,pastYearVo.getSignPlanNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,pastYearVo.getSignPlanArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,pastYearVo.getSignAvgPlanPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,pastYearVo.getSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,pastYearVo.getSignActualNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,pastYearVo.getSignActualArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,pastYearVo.getSignAvgActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,pastYearVo.getSignActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,pastYearVo.getSignFillRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,pastYearVo.getActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,pastYearVo.getPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
        //月度全盘签约
        List<MonthSignVo> MonthVoList = vo.getMonthSignVo();
        if(CollectionUtils.isNotEmpty(MonthVoList)) {
            for(int i = 0 ; i < MonthVoList.size() ; i++) {
                //月度合计
                MonthSignVo monthSignVo = MonthVoList.get(i);
                this.appendCell(row, index++,monthSignVo.getEarlyNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlyArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getAvgEarlyPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlyPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlySignPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlyAvgSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlySignActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getEarlyAvgSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getEarlySignFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getEarlyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getEarlyPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplyFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplySignPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplySignPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplySignActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSupplyAvgSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSupplySignFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSupplyPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailableNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailableArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvailablePrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignPlanNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignPlanArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvgSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignActualNum(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignActualArea(),isParentLevel,NUMERIC_TWO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalAvgSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignActualPrice(),isParentLevel,NUMERIC_ZERO);
                this.appendCell(row, index++,monthSignVo.getSyntheticalSignFillRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSyntheticalActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
                this.appendCell(row, index++,monthSignVo.getSyntheticalPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
            }
        }
        //年度合计
        YearSignVo yearSignVo = vo.getYearSignVo();
        this.appendCell(row, index++,yearSignVo.getEarlyNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getEarlyArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getEarlyAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getEarlyPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getEarlySignActualNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getEarlySignActualArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getEarlySignActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getEarlySignActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getEarlyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,yearSignVo.getSupplyPlanNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSupplyPlanArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSupplyPlanAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSupplyPlanPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSupplyActualNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSupplyActualArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSupplyActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSupplyActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSupplyFillRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,yearSignVo.getSupplySignActualNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSupplySignActualArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSupplySignActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSupplySignActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSupplyActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,yearSignVo.getSyntheticalAvailableNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalAvailableArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalAvailablePrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualNum(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualArea(),isParentLevel,NUMERIC_TWO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualAvgPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignActualPrice(),isParentLevel,NUMERIC_ZERO);
        this.appendCell(row, index++,yearSignVo.getSyntheticalSignFillRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,yearSignVo.getSyntheticalActualRemovalRate(),isParentLevel,ACHIEVING_RATE);
        this.appendCell(row, index++,yearSignVo.getSyntheticalPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);

        //季度签约
        List<QuarterSignVo> quarterSignVoList = vo.getQuarterSignVo();
        for (QuarterSignVo quarterSign : quarterSignVoList){
            this.appendCell(row, index++,quarterSign.getEarlyNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getEarlyArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getEarlyAvgPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getEarlyPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getSupplyNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getSupplyArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getSupplyAvgPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getSupplyPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getAvailableNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getAvailableArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getAvailablePrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getSignPlanNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getSignPlanArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,quarterSign.getSignAvgPlanPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,quarterSign.getSyntheticalPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
        }

        //年度合计签约
        List<YearTotalVo> yearTotalVoList = vo.getYearTotalVo();
        for (YearTotalVo yearTotalVo: yearTotalVoList){
            this.appendCell(row, index++,yearTotalVo.getEarlyNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getEarlyArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getEarlyAvgPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getEarlyPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getSupplyNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getSupplyArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getSupplyAvgPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getSupplyPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getAvailableNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getAvailableArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getAvailableAvgPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getAvailablePrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getSignPlanNum(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getSignPlanArea(),isParentLevel,NUMERIC_TWO);
            this.appendCell(row, index++,yearTotalVo.getSignAvgPlanPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getSignPlanPrice(),isParentLevel,NUMERIC_ZERO);
            this.appendCell(row, index++,yearTotalVo.getSyntheticalPlanRemovalRate(),isParentLevel,ACHIEVING_RATE);
        }
        currentDataRowCursor++;
    }

    /**
     * 插入列
     * @param row 行
     * @param index 列数
     * @param value 值
     * @param isParentLevel 是否汇总行
     * @param type 样式类型
     */
    private void appendCell(XSSFRow row, int index, String value, boolean isParentLevel, String type) {
        XSSFCell cell = row.getCell(index);
        if (cell == null) {
            cell = row.createCell(index);
        }

        if(isParentLevel){
            switch (type){
                case EQUITY_RATIO:
                    cell.setCellStyle(cellStyleA2);
                    if(value == null){
                        cell.setCellValue("");
                    }else {
                        cell.setCellValue(Double.valueOf(value)/100);
                    }
                    break;
                case NUMERIC_TWO:
                    if(value.contains("/")){
                        cell.setCellStyle(cellStyleA5);
                        cell.setCellValue(value);
                    }else {
                        cell.setCellStyle(cellStyleA3);
                        cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace(",","")));
                    }
                    break;
                case NUMERIC_ZERO:
                    if(value.contains("/")){
                        cell.setCellStyle(cellStyleA5);
                        cell.setCellValue(value);
                    }else {
                        cell.setCellStyle(cellStyleA4);
                        cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace(",","")));
                    }
                    break;
                case ACHIEVING_RATE:
                    cell.setCellStyle(cellStyleA6);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace("%","").replace(",",""))/100);
                    break;
                default:
                    cell.setCellStyle(cellStyleA1);
                    cell.setCellValue(StrUtils.isEmpty(value) ? "" : value);
                    break;
            }

        }else {
            switch (type){
                case EQUITY_RATIO:
                    cell.setCellStyle(cellStyleB2);
                    if(value == null){
                        cell.setCellValue("");
                    }else {
                        cell.setCellValue(Double.valueOf(value)/100);
                    }
                    break;
                case NUMERIC_TWO:
                    cell.setCellStyle(cellStyleB3);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value));
                    break;
                case NUMERIC_ZERO:
                    cell.setCellStyle(cellStyleB4);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value));
                    break;
                case ACHIEVING_RATE:
                    cell.setCellStyle(cellStyleB5);
                    cell.setCellValue(StrUtils.isEmpty(value) ? 0: Double.valueOf(value.replace("%","").replace(",",""))/100);
                    break;
                default:
                    cell.setCellStyle(cellStyleB1);
                    cell.setCellValue(StrUtils.isEmpty(value) ? "" : value);
                    break;
            }
        }
    }


    /**
     * 导出
     *
     * @param outputStream
     * @throws IOException
     */
    public void write(OutputStream outputStream) throws IOException {
        workbook.write(outputStream);
    }
}
